require 'rubygems'
require 'sqlite3'
require 'singleton'
require 'pelicula'
require 'Contexto'
require 'Mensajes'
require 'MdbException'

class DbAccess
  include Singleton
  
  def initialize
    begin
      @db = SQLite3::Database.new(Contexto.instance.db)
	rescue SQLite3::SQLException => sqle
	  raise MdbException.new Mensajes.instance.exDbUp, sqle
	end
  end
  
  def dvds
    dvds = []
	begin
      @db.execute("SELECT DISTINCT dvd FROM mdb_peliculas_x_dvd").each do |dvd|
	    dvds << dvd[0]
	  end
	rescue SQLite3::SQLException => sqle
	  raise MdbException.new Mensajes.instance.exBuscaDvd, sqle
	end
	return dvds
  end

  def addPelicula peli
    begin
	  @db.transaction do
        @db.execute("INSERT INTO MDB_PELICULAS VALUES ('#{peli.titulo}', #{peli.anno.to_i}, '#{peli.director}', '#{peli.sinopsis}')")
	  end
	rescue SQLite3::SQLException => sqle
	  raise MdbException.new Mensajes.instance.exAgregarPelicula, sqle
	end
  end
  
  def addPelicula_x_Dvd (peli, dvd)
    begin
      @db.transaction do
        @db.execute("INSERT INTO MDB_PELICULAS_X_DVD VALUES('#{dvd}', '#{peli.titulo}', #{peli.anno.to_i}, 'N')")
      end
	rescue SQLite3::SQLException => sqle
	  raise MdbException.new Mensajes.instance.exAgregarPeliculaDvd, sqle
	end
  end
  
  def peliculas pelicula
    dvds = {}
	sql = "SELECT titulo, anno, director, sinopsis FROM mdb_peliculas WHERE "
	parametros = 0
	if !pelicula.titulo.nil?
	  sql << "titulo = '#{pelicula.titulo}'"
	  parametros += 1
	end
	if pelicula.anno != 0
	  if parametros == 0
	    sql << " AND "
	  end
	  sql << "anno = #{pelicula.anno}"
	end
	if !pelicula.director.nil?
	  if parametros == 0
	    sql << " AND "
	  end
	  sql << "director = '#{pelicula.director}'"
	  parametros+=1
	end
	begin
      stmt = @db.prepare("SELECT dvd FROM mdb_peliculas_x_dvd WHERE titulo = ? AND anno = ?")
      @db.execute(sql).each do |peli|
	    dvd = stmt.get_first_row peli[0], peli[1]
	    newPeli = Pelicula.new
	    newPeli.titulo = peli[0]
	    newPeli.anno = peli[1]
	    newPeli.director = peli[2]
	    newPeli.sinopsis = peli[3]

	    donde = "Disco"
	    if !(dvd[0].nil? or dvd[0] == "")
	      donde = dvd[0]
	    end
        peliculas[donde] << newPeli
	  end
	rescue SQLite3::SQLException => sqle
	  raise MdbException.new Mensajes.instance.exBuscarPeliculas, sqle
	end
	return peliculas
  end
  
  def peliculasXdvd pelicula, annoMin, annoMax, dvd
    dvds = {}
    sql = "SELECT a.titulo, a.anno, a.director, a.sinopsis, b.dvd FROM mdb_peliculas a, mdb_peliculas_x_dvd b WHERE a.titulo = b.titulo AND a.anno = b.anno"
    if !(dvd.nil? or dvd == "")
      sql << " AND b.dvd = '#{dvd}'"
    end
	if !pelicula.nil?
      if !(pelicula.titulo.nil? or pelicula.titulo == "")
        sql << " AND a.titulo like '%#{pelicula.titulo}%'"
      end
      if !(pelicula.director.nil? or pelicula.director == "")
        sql << " AND a.director like '%#{pelicula.director}%'"
      end
	end
	if !(annoMin.nil? or annoMin == 0)
	  sql << " AND a.anno >= #{annoMin}"
	end
	if !(annoMax.nil? or annoMax == 0)
	  sql << " AND a.anno <= #{annoMax}"
	end

	begin
	  @db.execute(sql).each do |peli_x_dvd|
	    peli = Pelicula.new
	    peli.titulo = peli_x_dvd[0]
	    peli.anno = peli_x_dvd[1].to_i
	    peli.director = peli_x_dvd[2]
	    peli.sinopsis = peli_x_dvd[3]

	    if dvds[peli_x_dvd[4]].nil?
	      dvds[peli_x_dvd[4]] = []
	    end
	    dvds[peli_x_dvd[4]] << peli
	  end
	rescue SQLite3::SQLException => sqle
	  raise MdbException.new Mensajes.instance.exBuscaPelicula, sqle
	end

	return dvds
  end

  def pelicula pelicula
    peliculas = []
	sql = "SELECT titulo, anno, director, sinopsis FROM mdb_peliculas WHERE "
	parametros = 0
	if pelicula.nil?
	  sql << "1 = 1"
	else
	  if !(pelicula.titulo.nil? or pelicula.titulo == "")
	    sql << "titulo = '#{pelicula.titulo}'"
	    parametros += 1
	  end
	  if !(pelicula.anno.nil? or pelicula.anno == 0)
	    if parametros == 0
	      sql << " AND "
	    end
	    sql << "anno = #{pelicula.anno}"
		parametros+=1
	  end
	  if !(pelicula.director.nil? or pelicula.director == "")
	    if parametros == 0
	      sql << " AND "
	    end
	    sql << "director = '#{pelicula.director}'"
	    parametros+=1
	  end
	end
	begin
      @db.execute(sql).each do |peli|
	    newPeli = Pelicula.new
	    newPeli.titulo = peli[0]
	    newPeli.anno = peli[1]
	    newPeli.director = peli[2]
	    newPeli.sinopsis = peli[3]

	    peliculas << newPeli
	  end
	rescue SQLite3::SQLException => sqle
	  raise MdbException.new Mensajes.instance.exBuscaPelicula, sqle
	end
	return peliculas
  end
  
  def addPeliculas peliculasXdvd
    stmtPeliculas = @db.prepare "INSERT INTO mdb_peliculas (titulo, anno, director, sinopsis) VALUES (?, ?, ?, ?)"
	stmtDvd = @db.prepare "INSERT INTO mdb_peliculas_x_dvd (titulo, anno, dvd, prestado) VALUES (?, ?, ?, 'N')"
	
	peliculasUpdate = []
	
	peliculasXdvd.each do |dvd, peliculas|
	  peliculas.each do |peli|
	    begin
	      stmtPeliculas.execute peli.titulo, peli.anno, peli.director, peli.sinopsis
		  stmtDvd.execute peli.titulo, peli.anno, dvd
		rescue SQLite3::SQLException => sqle
		  peliculasUpdate << peli
		end
	  end
	end
	
	stmtUpd = @db.prepare "UPDATE mdb_peliculas SET director = ?, sinopsis = ? WHERE titulo = ? AND anno = ?"
	peliculasUpdate.each do |peli|
	  begin
	    stmtUpd.execute peli.director, peli.sinopsis, peli.titulo, peli.anno
      rescue SQLite3::SQLException => sqle
	    raise MdbException.new Mensajes.instance.exAgregarPelicula, sqle
	  end
	end
  end

  def updPelicula pelicula, dvd
    begin
	  @db.transaction do
	    @db.execute "DELETE FROM mdb_peliculas WHERE titulo = '#{pelicula.titulo}' AND anno = #{pelicula.anno}"
	    @db.execute "DELETE FROM mdb_peliculas_x_dvd WHERE titulo = '#{pelicula.titulo}' AND anno = #{pelicula.anno}"
	  end
	  self.addPelicula pelicula
	  if !dvd.nil?
	    self.addPelicula_x_Dvd pelicula, dvd
	  end
	rescue SQLite3::SQLException => sqle
	  raise MdbException.new Mensajes.instance.exAgregarPelicula, sqle
	end
  end

  def generos
    generos = []
    begin
      @db.execute("SELECT genero FROM mdb_generos ORDER BY genero ASC").each {|genero| generos << genero[0]}
    rescue SQLite3::SQLException => sqle
      raise MdbException.new Mensajes.instance.exAgregarPelicula, sqle
    end
  end

end #class DbAccess
